package cn.wangsy.zhidao.p20170315;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class JdbcTest {

	private static String driverClassName;
	private static String schema;
	private static String url;
	private static String username;
	private static String password;

	static {
		Properties prop = new Properties();
		InputStream in = JdbcTest.class.getClassLoader().getResourceAsStream(
				"cn/wangsy/zhidao/p20170315/jdbc.properties");
		try {
			prop.load(in);
			driverClassName = prop.getProperty("jdbc.driverClassName").trim();
			schema = prop.getProperty("jdbc.schema").trim();
			url = prop.getProperty("jdbc.url").trim();
			username = prop.getProperty("jdbc.username").trim();
			password = prop.getProperty("jdbc.password").trim();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		try {
			Class.forName(driverClassName);
			// String schema = "blog";
			Connection connection = DriverManager.getConnection(url, username,
					password);

			List<Table> tables = new ArrayList<Table>();

			Statement statement = connection.createStatement();
			ResultSet rs = statement
					.executeQuery("select TABLE_NAME,TABLE_COMMENT from information_schema.tables where table_schema='"
							+ schema + "' and table_type='base table';");
			while (rs.next()) {

				Table table = new Table();

				String tableName = rs.getString("TABLE_NAME");
				String comment = rs.getString("TABLE_COMMENT");
				if (StringUtils.isNotBlank(comment)) {
					System.out.println(tableName + "(" + comment + ")");
				} else {
					System.out.println(tableName);
				}
				table.setName(tableName);
				table.setComment(comment);

				List<Column> columns = new ArrayList<Column>();
				Statement colStatement = connection.createStatement();
				ResultSet columnResultSet = colStatement
						.executeQuery("select column_name,column_comment from information_schema.columns where table_schema='"
								+ schema
								+ "' and table_name='"
								+ tableName
								+ "'");
				while (columnResultSet.next()) {
					Column column = new Column();
					String columnName = columnResultSet
							.getString("column_name");
					System.out.print(columnName + "/t");
					column.setName(columnName);
					column.setComment(columnResultSet
							.getString("column_comment"));
					columns.add(column);
				}
				table.setColumns(columns);
				columnResultSet.close();
				System.out.println();

				List<Map<String, Object>> dataMap = new ArrayList<Map<String, Object>>();

				Statement dataStatement = connection.createStatement();
				ResultSet dataResultSet = dataStatement
						.executeQuery("select * from " + tableName);
				while (dataResultSet.next()) {
					Map<String, Object> map = new HashMap<String, Object>();
					for (Column col : columns) {
						String value = dataResultSet.getString(col.getName());
						System.out.print(value + "/t");
						map.put(col.getName(), value);
					}
					System.out.println();
					dataMap.add(map);
				}
				table.setData(dataMap);
				dataResultSet.close();

				tables.add(table);
			}
			rs.close();

			System.out.println(tables);
			outFile("", tables);

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void outFile(String path, List<Table> list) {

		// 创建HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();

		for (Table table : list) {
			// 创建HSSFSheet对象
			String sheetName = "";
			if (StringUtils.isNotBlank(table.getComment())) {
				sheetName = table.getName() + "(" + table.getComment() + ")";
			} else {
				sheetName = table.getName();
			}
			HSSFSheet sheet = wb.createSheet(sheetName);

			// 创建字段行
			HSSFRow row = sheet.createRow(0);
			for (int i = 0; i < table.getColumns().size(); i++) {
				HSSFCell cell = row.createCell(i);
				Column column = table.getColumns().get(i);
				String value = "";
				if (StringUtils.isNotBlank(column.getComment())) {
					value = column.getName() + "(" + column.getComment() + ")";
				} else {
					value = column.getName();
				}
				cell.setCellValue(value);
			}

			for (int i = 0; i < table.getData().size(); i++) {
				Map<String, Object> data = table.getData().get(i);
				HSSFRow valueRow = sheet.createRow(i + 1);
				for (int j = 0; j < table.getColumns().size(); j++) {
					Column column = table.getColumns().get(j);
					HSSFCell cell = valueRow.createCell(j);
					cell.setCellValue(data.get(column.getName()) == null ? ""
							: data.get(column.getName()).toString());
				}
			}

		}
		// 输出Excel文件
		FileOutputStream output;
		try {
			output = new FileOutputStream("d:\\workbook.xls");
			wb.write(output);
			output.flush();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

}
